package cn.com.elitecrm.service;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.elitecrm.util.DateUtil;
import com.elitecrm.util.FTPUtil;
import com.elitecrm.util.OgnlUtil;
import com.elitecrm.util.StringUtil;

public class FileGSService {
	private static final Log log = LogFactory.getLog(FileGSService.class);
	private DataSource dataSource;
	private FileGenerator fileGenerator;
	private String scanFtpPathSql;
	private String updateSql;
	private Map<String,Object> context;
	String fileName1="";
	String FTPPath1="";
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public FileGenerator getFileGenerator() {
		return fileGenerator;
	}
	public void setFileGenerator(FileGenerator fileGenerator) {
		this.fileGenerator = fileGenerator;
	}
	public String getUpdateSql() {
		return updateSql;
	}
	public void setUpdateSql(String updateSql) {
		this.updateSql = updateSql;
	}
	public String getScanFtpPathSql() {
		return scanFtpPathSql;
	}
	public void setScanFtpPathSql(String scanFtpPathSql) {
		this.scanFtpPathSql = scanFtpPathSql;
	}
	//	1.从数据库根据SQL读取数据，自动生成txt
//	PS:SQL、分隔符，编码格式 （希望做成可配）
//	2.自动定时运行推送,可按照，秒，分，小时，年，月，日为间隔单位，进行配置，自动定时运行推送生成的txt
//	3.生成txt文件存放路径可配置
//	4.可将生成的txt文件，直接推送到指定的FTP地址
//	5.自动定时运行扫描，可按照，秒，分，小时，年，月，日为间隔单位，进行配置，自动定时运行扫描指定的FTP服务器路径，并且将扫描结果回写
	
	public void generateAndSendFile2(){
		try{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");   
			String temp_str=sdf.format(new Date());
			log.debug("temp_str:"+temp_str);
			JdbcTemplate jt = new JdbcTemplate(dataSource);
			String sql=fileGenerator.getQuerySql_all();
			log.debug("sql: "+sql);
			List list=jt.queryForList(sql);
			if(list!=null&&list.size()>0){
				for(int i=0;i<list.size();i++){
					Map map=(Map)list.get(i);
					String querySql=(String)map.get("FTPSQL");
					log.debug("querySql: "+querySql);
					String fileName=(String)map.get("custlist_xxx");
					log.debug("fileName: "+fileName);
					String FTPPath=(String)map.get("FTPPath");
					log.debug("FTPPath: "+FTPPath);
//					FTPPath=FTPPath+"/"+temp_str;
//					log.debug("FTPPath: "+FTPPath);
					generateAndSendFile1(querySql,fileName,FTPPath);
				}
			}
		}catch(Exception e){
			log.error("",e);
		}
	}
	
	public void generateAndSendFile1(String querySql,String fileName,String FTPPath){
		log.info("===============  Start generate and send file2  ===============");
		fileName1=fileName;
		log.debug("fileGenerator.getGenerateFileNamePattern():"+fileGenerator.getGenerateFileNamePattern());
		fileName = fileName+fileGenerator.getGenerateFileNamePattern();
		log.debug("fileName:"+fileName);
		FTPPath1=FTPPath;
		try{
			JdbcTemplate jt = new JdbcTemplate(dataSource);
			log.debug("Query sql: "+querySql);
			String fileFolderPath = fileGenerator.getGeneratePath();
			final String fileRealPath = fileFolderPath+File.separator+fileName;
			log.debug("Generate file path: "+fileRealPath);
			boolean generateFileResult = jt.query(querySql, new ResultSetExtractor<Boolean>(){
				public Boolean extractData(ResultSet rs) throws SQLException,
						DataAccessException {
					boolean r = false;
					int columnCount = rs.getMetaData().getColumnCount();
					BufferedWriter bw = null;
					log.debug("Start generate file.");
					
					
					try{
						bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileRealPath),fileGenerator.getEncoding()));
						log.debug("fileGenerator.isNeedHead():"+fileGenerator.isNeedHead());
						if(fileGenerator.isNeedHead()){
							StringBuffer headSB = new StringBuffer();
							log.debug("columnCount:"+columnCount);
							for(int i=1;i<=columnCount;i++){
								String columnName = rs.getMetaData().getColumnName(i);
								if(i==columnCount){
									headSB.append(columnName+"\n");
								}else{
									Map<String,Object> context = new HashMap<String,Object>();
									context.put("tab", "\t");
									String separator = OgnlUtil.parseString(fileGenerator.getSeparator(), context);
									headSB.append(columnName+separator);
								}
							}
							log.debug("Generate head: "+headSB.toString());
							bw.write(headSB.toString());
						}

						while(rs.next()){
							StringBuffer sb = new StringBuffer();
							String result = "";
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							for(int i=1;i<=columnCount;i++){
								int columnType = rs.getMetaData().getColumnType(i);
								result = getResultStringByType(columnType,i,rs,sdf);
								if(i==columnCount){
									sb.append(result+"\n");
								}else{
									Map<String,Object> context = new HashMap<String,Object>();
									context.put("tab", "\t");
									String separator = OgnlUtil.parseString(fileGenerator.getSeparator(), context);
									sb.append(result+separator);
								}
							}
							log.debug("Generate one row: "+sb.toString());
							bw.write(sb.toString());
						}
						bw.flush();
						r = true;
						log.debug("End generate file.");
						JdbcTemplate jt1 = new JdbcTemplate(dataSource);
						String update_sql="update Pdscustlists set usestatus='6' where custlist_xxx='"+fileName1+"'";
						log.debug("update_sql:"+update_sql);
						int update_result=jt1.update(update_sql);
						log.debug("update_result:"+update_result);
					}catch(Exception e){
						log.error("",e);
					}finally{
						if(bw!=null){
							try{bw.close();}catch(Exception e){}
						}
					}
					return r;
				}
			});
			if(generateFileResult){
				log.debug("Start send file: "+fileRealPath);
				FTPUtil ftpUtil = new FTPUtil(fileGenerator.getFtpIp(),fileGenerator.getFtpUsername(),fileGenerator.getFtpPassword(),fileGenerator.getFtpPort());
				
				boolean uploadResult = ftpUtil.upload(fileRealPath, FTPPath);
				log.debug("End send file, result "+uploadResult);
			}
		}catch(Exception e){
			log.error("",e);
		}
		log.info("End generate and send file");
	}
	
	public void generateAndSendFile(){
		log.info("===============  Start generate and send file  ===============");
		
		try{
			JdbcTemplate jt = new JdbcTemplate(dataSource);
			String querySql = fileGenerator.getQuerySql();
			log.debug("Query sql: "+querySql);
			String fileFolderPath = fileGenerator.getGeneratePath();
			String fileName = generateString(fileGenerator.getGenerateFileNamePattern());
			final String fileRealPath = fileFolderPath+File.separator+fileName;
			log.debug("Generate file path: "+fileRealPath);
			boolean generateFileResult = jt.query(querySql, new ResultSetExtractor<Boolean>(){
				public Boolean extractData(ResultSet rs) throws SQLException,
						DataAccessException {
					boolean r = false;
					int columnCount = rs.getMetaData().getColumnCount();
					BufferedWriter bw = null;
					log.debug("Start generate file.");
					
					
					try{
						bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileRealPath),fileGenerator.getEncoding()));
						
						if(fileGenerator.isNeedHead()){
							StringBuffer headSB = new StringBuffer();
							for(int i=1;i<columnCount;i++){
								String columnName = rs.getMetaData().getColumnName(i);
								if(i==columnCount){
									headSB.append(columnName+"\n");
								}else{
									Map<String,Object> context = new HashMap<String,Object>();
									context.put("tab", "\t");
									String separator = OgnlUtil.parseString(fileGenerator.getSeparator(), context);
									headSB.append(columnName+separator);
								}
							}
							log.debug("Generate head: "+headSB.toString());
							bw.write(headSB.toString());
						}

						while(rs.next()){
							StringBuffer sb = new StringBuffer();
							String result = "";
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							for(int i=1;i<=columnCount;i++){
								int columnType = rs.getMetaData().getColumnType(i);
								result = getResultStringByType(columnType,i,rs,sdf);
								if(i==columnCount){
									sb.append(result+"\n");
								}else{
									Map<String,Object> context = new HashMap<String,Object>();
									context.put("tab", "\t");
									String separator = OgnlUtil.parseString(fileGenerator.getSeparator(), context);
									sb.append(result+separator);
								}
							}
							log.debug("Generate one row: "+sb.toString());
							bw.write(sb.toString());
						}
						bw.flush();
						r = true;
						log.debug("End generate file.");
					}catch(Exception e){
						log.error("",e);
					}finally{
						if(bw!=null){
							try{bw.close();}catch(Exception e){}
						}
					}
					return r;
				}
			});
			if(generateFileResult){
				log.debug("Start send file: "+fileRealPath);
				FTPUtil ftpUtil = new FTPUtil(fileGenerator.getFtpIp(),fileGenerator.getFtpUsername(),fileGenerator.getFtpPassword(),fileGenerator.getFtpPort());
				
				boolean uploadResult = ftpUtil.upload(fileRealPath, fileGenerator.getFtpPath());
				log.debug("End send file, result "+uploadResult);
			}
		}catch(Exception e){
			log.error("",e);
		}
		log.info("End generate and send file");
	}
	
	
	public void scanFTP() throws IOException{
		log.info("===============  Start scan ===============");
		FTPUtil ftpUtil = null;
		try{
			ftpUtil = new FTPUtil(fileGenerator.getFtpIp(),fileGenerator.getFtpUsername(),fileGenerator.getFtpPassword(),fileGenerator.getFtpPort());
			if(StringUtil.isNotNull(scanFtpPathSql)){
				JdbcTemplate jt = new JdbcTemplate(dataSource);
				List<String> ftpPathList = jt.query(scanFtpPathSql, new ResultSetExtractor<List<String>>(){
					public List<String> extractData(ResultSet rs) throws SQLException,
							DataAccessException {
						List<String> result = new ArrayList<String>();
						while(rs.next()){
							result.add(rs.getString(1));
						}
						return result;
					}
				});
				
				for(String ftpPath : ftpPathList){
					log.debug("scan path: "+ftpPath);
					List<String> fileNames = ftpUtil.listFileNames(ftpPath);
					if(fileNames!=null && fileNames.size()>0){
//						String fileName = fileNames.get(0);
//						int affectCount = jt.update(updateSql, fileName);
//						log.debug("Execute sql: "+updateSql+" with fileName "+fileName+" affect count "+affectCount);
					}else{
						log.debug("ftpPath:"+ftpPath);
						String fileName = ftpPath.substring(ftpPath.lastIndexOf("/")+1, ftpPath.length());
						log.debug("fileName:"+fileName);
						fileName = fileName.substring(0,fileName.indexOf("."));
						log.debug("fileName:"+fileName);
						int affectCount = jt.update(updateSql, fileName);
						log.debug("Execute sql: "+updateSql+" with fileName "+fileName+" affect count "+affectCount);
					}
				}
			}
		}catch(Exception e){
			log.error("",e);
		}finally{
			log.debug("-------finally--------");
			if(ftpUtil!=null){
				ftpUtil.disconnect();
				log.debug("-------ftpUtil.disconnect()--------");
			}
		}
		log.info("End scan");
	}	
	public static void main(String[] args){
		String ftpPath="/autoImport/60001/Z20130711001CS004006F195500000.txt";
		String fileName = ftpPath.substring(ftpPath.lastIndexOf("/")+1, ftpPath.length());
		System.out.println("fileName:"+fileName);
		fileName = fileName.substring(0,fileName.indexOf("."));
		System.out.println("fileName:"+fileName);
	}
	private String getResultStringByType(int columnType,int i,ResultSet rs,SimpleDateFormat sdf) throws SQLException{
		String result = "";
		switch(columnType){
			case java.sql.Types.VARCHAR:
			case java.sql.Types.CHAR:
			case java.sql.Types.LONGVARCHAR:
			case java.sql.Types.CLOB:
				result = rs.getString(i);
				break;
			case java.sql.Types.NUMERIC:
				result = rs.getInt(i)+"";
				break;
			case java.sql.Types.BIT:
			case java.sql.Types.TINYINT:
			case java.sql.Types.SMALLINT:
			case java.sql.Types.INTEGER:
			case java.sql.Types.BIGINT:
				
			case java.sql.Types.DECIMAL:
				result = rs.getInt(i)+"";
				break;
			case java.sql.Types.FLOAT:
			case java.sql.Types.REAL:
			case java.sql.Types.DOUBLE:
				result = rs.getDouble(i)+"";
				break;
			case java.sql.Types.DATE:
			case java.sql.Types.TIME:
			case java.sql.Types.TIMESTAMP:
				Timestamp ts = rs.getTimestamp(i);
				if(ts!=null){
					result = sdf.format(ts);
				}				
				break;
			default:
				result = rs.getString(i);
				break;
		}
		return result;
	}
	
	private String generateString(String pattern){
		if(context==null){
			context = new HashMap<String,Object>();
			String nowDate = DateUtil.formatDate(new Date(), "yyyyMMdd");
			context.put("nowDate", nowDate);
			if(StringUtil.isNotNull(fileGenerator.getGenerateFileNameSql())){
				JdbcTemplate jt = new JdbcTemplate(dataSource);
				String fileNameFromDB = jt.query(fileGenerator.getGenerateFileNameSql(), new ResultSetExtractor<String>(){
					public String extractData(ResultSet rs) throws SQLException,
							DataAccessException {
						String result = null;
						if(rs.next()){
							result = rs.getString(1);
						}
						return result;
					}
				});
				context.put("fileNameFromDB", fileNameFromDB);
			}
			if(StringUtil.isNotNull(fileGenerator.getFtpPathSql())){
				JdbcTemplate jt = new JdbcTemplate(dataSource);
				String ftpPathFromDB = jt.query(fileGenerator.getFtpPathSql(), new ResultSetExtractor<String>(){
					public String extractData(ResultSet rs) throws SQLException,
							DataAccessException {
						String result = null;
						if(rs.next()){
							result = rs.getString(1);
						}
						return result;
					}
				});
				context.put("ftpPathFromDB", ftpPathFromDB);
			}
		}
		
		return OgnlUtil.parseString(pattern, context);
	}
}
